<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2020 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://demo.thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\cms\controller;


use function PHPSTORM_META\type;
use think\admin\Controller;
use think\facade\Config;
use think\admin\service\AdminService;
use think\admin\extend\DataExtend;
use think\admin\service\MenuService;




/**
 * 档案管理
 * Class Educert
 * @package app\admin\controller
 */
class Educert extends BaseController
{

    /**
     * 当前操作数据库
     * @var string
     */
    protected $table = 'CmsEduCert';
    static $PASS_STATE_OPTION = array(0=>"待考核",1=>"合格",2=>"不合格");
    public $CITY_OPTION = array("长沙","株洲","湘潭","益阳","衡阳","常德","岳阳","邵阳","娄底","永州","郴州","怀化","张家界","湘西自治州");

    /**
     * 档案管理
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function index()
    {

        $this->title = "档案管理";
        $this->city = $this->app->request->request("city");
        $map = ['is_deleted' => '0'];
        $query = $this->_query($this->table)->like('name,company,idno,class_name,city')->where($map);

        $this->time_span = $this->request->get("time_span");
        if($this->time_span){
            $time_span = explode(" - ", $this->time_span);
            $query = $query->whereBetween("cert_date",[$time_span[0],$time_span[1]]);
        }
        $query->order('id desc')->page();
    }

    protected function _getClassDict($class_ids){
        $map = array("id"=> $class_ids);
        $class = $this->app->db->name('CmsEduClass')->field("id,name")->where($map)->select();
        $result = array();
        for ($i=0; $i<count($class); $i++){
            $result[$class[$i]['id']] = $class[$i]['name'];
        }
        return $result;
    }

    protected function _getDataIds($data, $key){
        $arr = [];
        for ($i=0; $i<count($data); $i++){
            array_push($arr, $data[$i][$key]);
        }
        return $arr;
    }

    /**
     * 表单数据处理
     * @param array $vo
     * @throws \ReflectionException
     */
    protected function _form_filter(&$vo)
    {
        if ($this->request->isGet()) {
            // 清理权限节点
            AdminService::instance()->clearCache();
            if($vo && $vo['class_id'])
                $map = array("id"=>$vo['class_id']);
            else
                $map = array();
            if($vo){
                $this->enroll = $vo;
            }else{
                $this->enroll = array("cert_no"=>"","name"=>"","idno"=>"","gender"=>"男","class_name"=>"","memo"=>"",
                    "company"=>"","class_id"=>"","position_id"=>"","work_name"=>"","cert_date"=>"","cert_org"=>"");
            }

            $this->classes = $this->_get_class_enroll();
            for($i=0; $i<count($this->classes); $i++){
                $this->classes[$i]['sub'] = $this->_get_work_ids($this->classes[$i]['position_ids']);
            }
            $this->classesJson = json_encode($this->classes);
            /*
            $this->class = $this->app->db->name("CmsEduClass")->where($map)->find();
            if($this->class){
                $this->work = $this->_get_work_ids($this->class['position_ids']);
                $this->workJson = json_encode($this->work);
            }else{
                $this->work = $this->_get_work_ids("");
                $this->workJson = json_encode($this->work);
            }*/
        }
    }

    //查询所有正在接受报名的开班信息
    public function _get_class_enroll(){
        $map = array("status"=>1);
        return $this->app->db->name('CmsEduClass')->field("id,name,position_ids")->where($map)
            ->whereBetween("create_at",[ time()-6*30*24*3600, time() ])
            ->select()->toArray();
    }

    public function _get_work_ids($position_ids){
        $work_ids = [];
        $position = $this->app->db->name('CmsEduWork')->alias("wp")
            ->field("wp.*,ew.name as class_name, ew.id as class_id")
            ->join('cms_edu_work ew', "ew.id=wp.parent_id")
            ->whereIn("wp.id", $position_ids)
            ->order('wp.id desc')->select()->toArray();

        for($i=0; $i<count($position); $i++){
            if(array_key_exists($position[$i]['class_id'], $work_ids)==false){
                $work_ids[ $position[$i]['class_id'] ] = array(
                    "id" => $position[$i]['class_id'],
                    "name" => $position[$i]['class_name'],
                    "sub" => [],
                );
            }
        }

        for($i=0; $i<count($position); $i++){
            $work_ids[ $position[$i]['class_id'] ]['sub'][] = $position[$i];
        }
        return array_values($work_ids);
    }

    /**
     * 添加档案
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function add()
    {

        if ($this->request->isGet()) {
            $this->_applyFormToken();
            $this->_form($this->table, 'form');
        } else {

            $update = $this->request->post('data', []);

            $map = [ "id"=>$update['class_id'] ];
            $class = $this->app->db->name("CmsEduClass")->where($map)->find();
            $update['class_name'] = $class['name'];

            $map = [ "position.id"=>$update['position_id'] ];
            $work= $this->app->db->name('CmsEduWork')->alias("position")
                ->field("position.*,work.name as work_name,work.id as work_id")
                ->join('cms_edu_work work', "position.parent_id=work.id")
                ->where($map)
                ->find();
            $update['position_name'] = $work['name'];
            $update['work_id'] = $work['work_id'];
            $update['work_name'] = $work['work_name'];

            $map = [ "idno"=>$update['idno'], "class_id"=>$update['class_id'] ];
            if($this->app->db->name("CmsEduCert")->where($map)->find()){
                $this->error("证书已经创建，不能重复创建");
            }

            $update[ 'create_by']   = AdminService::instance()->getUserId();
            $update[ 'update_at'] = $update[ 'create_at'] = time();
            if ($this->app->db->name($this->table)->insert($update) !== false) {
                $this->success('添加成功！','javascript:history.back()');
            } else {
                $this->error('添加失败，请稍候再试！');
            }
        }
    }

    /**
     * 编辑档案
     * @auth true
     */
    public function edit()
    {

        $this->id = $this->request->get('id');
        $map = array("id" => $this->id);
        if (empty($this->id)) $this->error('参数错误，请稍候再试！');
        if ($this->request->isGet()) {
            if ($this->request->get('output') === 'json') {
                $this->success('获取数据成功！', $this->app->db->name($this->table)->where($map)->find());
            } else {
                //$this->title = '编辑信息';
                $this->_applyFormToken();
                $this->_form($this->table, 'form');
            }
        }else{
            $data = $this->request->post('data', []);
            $data['update_at'] = time();
            if ($this->app->db->name($this->table)->where($map)->update($data) !== false) {
                $this->success('信息更新成功！');
            } else {
                $this->error('信息更新失败，请稍候再试！');
            }
        }

    }

    /**
     * 添加档案
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function addcert()
    {
        if ($this->request->isGet()) {
            $this->fetch();
        } else {
            $data = $this->request->post('data', []);
            $data[ 'update_at'] = $data[ 'create_at'] = time();
            //$map = [ "exam_no"=>$data['exam_no'] ];
            //$enroll_no=$this->app->db->name($this->table)->where($map)->find();
            $map = [ "no"=>$data['exam_no'] ];
            $enroll = $this->app->db->name("CmsEduExamEnroll")->where($map)->find();
            if($enroll==false){
                $this->error("无效的报名号：".$data['exam_no']);
            }

            $map = [ "exam_no"=>$data['exam_no'] ];
            if($this->app->db->name("CmsEduCert")->where($map)->find()){
                $this->error("报名号：".$data['exam_no']."已经创建了证书");
            }

            $map = [ "id"=>$enroll['class_id'] ];
            $class = $this->app->db->name("CmsEduClass")->field("id,name")->where($map)->find();

            $data['class_id'] = $class['id'];
            $data['class_name'] = $class['name'];
            $data['name'] = $enroll['name'];
            $data['gender'] = $enroll['gender'];
            $data['avatar'] = $enroll['avatar'];
            $data['company'] = $enroll['company'];
            $data['idno'] = $enroll['idno'];
            $data['work_id'] = $enroll['work_id'];
            $data['work_name'] = $enroll['work_name'];
            $data['position_id'] = $enroll['position_id'];
            $data['position_name'] = $enroll['position_name'];

            if ($this->app->db->name($this->table)->save($data)) {
                $this->success('创建成功！','javascript:history.back()');
            } else {
                $this->error('创建失败，请稍候再试！');
            }
        }
    }

    /**
     * 查看档案
     * @auth true
     */
    public function detail()
    {
        $this->id = $this->request->get('id');
        $map = array("id" => $this->id);
        $this->info = $this->app->db->name($this->table)->where($map)->find();
        $this->fetch();
    }

    /**
     * 档案复检
     * @auth true
     */
    public function doCheck(){
        $this->id = $this->request->get('id');
        $map = array("id" => $this->id);
        if (empty($this->id)) $this->error('参数错误，请稍候再试！');
        if ($this->request->isPost()) {
            $cert = $this->app->db->name($this->table)->where($map)->find();
            if($cert==false){
                $this->error("错误的请求参数");
            }
            $to_update = array(
                "check_num" => $cert['check_num']+1,
                "check_date" => date("Y-m-d"),
            );
            $this->app->db->name($this->table)->where($map)->update($to_update);
            $to_insert = array(
                "name" => $cert['name'],
                "gender" => $cert['gender'],
                "company" => $cert['company'],
                "idno" => $cert['idno'],
                "cert_no" => $cert['cert_no'],
                "cert_org" => $cert['cert_org'],
                "cert_date" => $cert['cert_date'],
                "work_name" => $cert['work_name'],
                "position_name" => $cert['position_name'],
                "create_at" => time(),
                "create_by" => session("user.username"),
            );
            $this->app->db->name("CmsEduCertCheck")->save($to_insert);
            $this->success("操作成功");
        }else{
            $this->error("无效的请求方式");
        }
    }

    /**
     * 删除档案
     * @auth true
     * @throws \think\db\exception\DbException
     */
    public function remove()
    {
        $this->_delete($this->table);
    }

    /**
     * 导出档案
     * @auth true
     */
    public function export(){

        $title = [
            array("cert_no","证书编号"),array("name","学员名称"), array("gender","性别"),array("idno","身份证号"),
            array("company","工作单位"),array("work_name","岗位类别"),array("position_name","工种"),
            array("cert_org","发证机关"),array("cert_date","发证日期"), array("check_num","复检次数	"),
            array("check_date","最后复检日期"),array("class_name","培训名称"),array("class_no","培训报名号"),
            array("exam_name","考核名称"),array("exam_no","考核报名号"),
        ];
        $data = $this->prepareExportData();
        $this->exportExcel($title, $data);

    }

    private function prepareExportData(){

        $map = ['is_deleted' => '0'];
        $query= $this->app->db->name('CmsEduCert');

        $class_name = $this->app->request->request("class_name");
        if($class_name) $query = $query->whereLike("class_name","%".$class_name."%");

        $name = $this->app->request->request("name");
        if($name) $query = $query->whereLike("name","%".$name."%");

        $idno = $this->app->request->request("idno");
        if($idno) $query = $query->whereLike("idno","%".$idno."%");

        $company = $this->app->request->request("company");
        if($company) $query = $query->whereLike("company","%".$company."%");

        $time_span = $this->app->request->request("time_span");
        if($time_span){
            $timeSpan = explode(" - ", $time_span);
            $query = $query->whereBetween("cert_date",[$timeSpan[0],$timeSpan[1]]);
        }
        $res = $query ->where($map)
            ->order('id desc')
            ->select()->toArray();
        return $res;
    }

    /**
     * 导入档案
     * @auth true
     */
    public function import(){
        //$inputFileName = "F:\\phpStudy\\GasPortalProject\\temp\\01simple.xlsx";
        //$inputFileName = "http://127.0.0.1:9500/upload/63/1fcfb478ee9a562f929784a1b44e64.xlsx";
        $inputFileName = $this->request->request("url");
        $inputFileName = $this->filerFilePath($inputFileName);

        $fields = [ "exam_no", "name", "cert_no", "cert_org", "cert_date", "memo" ];
        $data = $this->getImportDataFromExcel($inputFileName, $fields);

        //检查报名号是否有效
        for($i=0; $i<count($data); $i++){
            $map = [ "no"=>$data[$i]['exam_no'],"name"=>$data[$i]['name'] ];
            $data[$i]['enroll'] = $this->app->db->name("CmsEduExamEnroll")->where($map)->find();
            if($data[$i]['enroll']==false){
                $this->error("第".($i+2)."行数据有误，报名号：".$data[$i]['exam_no']."无效");
            }

            $map = [ "exam_no"=>$data[$i]['exam_no'] ];
            if($this->app->db->name("CmsEduCert")->where($map)->find()){
                $this->error("第".($i+2)."行数据有误，报名号：".$data[$i]['exam_no']."已经创建了证书");
            }

            $map = [ "id"=>$data[$i]['enroll']['exam_id'] ];
            $data[$i]['exam'] = $this->app->db->name("CmsEduExam")->field("id,name")->where($map)->find();
        }

        //将数据进行导入
        for($i=0; $i<count($data); $i++){
            $toImport = $this->_prepareImportData($data[$i]);
            $this->app->db->name($this->table)->save($toImport);
        }
        $this->success("导入成功");
    }

    /**
     * 导入历史档案
     * @auth true
     */
    public function importOld(){
        //$inputFileName = "F:\\phpStudy\\GasPortalProject\\temp\\01simple.xlsx";
        //$inputFileName = "http://127.0.0.1:9500/upload/63/1fcfb478ee9a562f929784a1b44e64.xlsx";
        $inputFileName = $this->request->request("url");
        $inputFileName = $this->filerFilePath($inputFileName);

        $fields = [ "name", "gender", "idno", "company", "work_name",
            "position_name", "cert_no", "cert_date", "memo" ];
        $data = $this->getImportDataFromExcel($inputFileName, $fields);

        //检查报名号是否有效
        for($i=0; $i<count($data); $i++){
            $map = [ "cert_no"=>$data[$i]['cert_no'] ];
            if($this->app->db->name("CmsEduCert")->where($map)->find()){
                $this->error("证书号：".$data[$i]['cert_no']."已经存在证书");
            }
        }

        //将数据进行导入
        for($i=0; $i<count($data); $i++){
            $toImport = $this->_prepareImportOldData($data[$i]);
            $this->app->db->name($this->table)->save($toImport);
        }
        $this->success("导入成功");
    }

    public function _prepareImportData($data){
        return array(
            "cert_no" =>$data['cert_no'],
            "cert_org" =>$data['cert_org'],
            "cert_date" =>$data['cert_date'],
            "enroll_no"  => $data['enroll']['exam_no'], //准考证号
            "memo" =>$data['memo'],

            "name" => $data['enroll']['name'],
            "gender" => $data['enroll']['gender'],
            "idno" => $data['enroll']['idno'],
            "avatar" => $data['enroll']['avatar'],
            "city" => $data['enroll']['city'],
            "company" => $data['enroll']['company'],
            "work_id" => $data['enroll']['work_id'],
            "work_name" => $data['enroll']['work_name'],
            "position_id" => $data['enroll']['position_id'],
            "position_name" => $data['enroll']['position_name'],

            "class_id" => $data['enroll']['class_id'],
            "class_no" => $data['enroll']['class_no'],
            "class_name" => $data['enroll']['class_name'],

            "exam_name" => $data['enroll']['exam_name'],
            "exam_no" => $data['enroll']['no'],
            "exam_id" => $data['enroll']['exam_id'],

            "update_at" => time(),
            "create_at" => time(),
            'create_by' => $data['enroll']['create_by'],
    );
    }

    public function _prepareImportOldData($data){
        return array(
            "cert_no" =>$data['cert_no'],
            //"cert_org" =>$data['cert_org'],
            "cert_date" =>$data['cert_date'],
            "memo" =>$data['memo'],

            "name" => $data['name'],
            "gender" => $data['gender'],
            "idno" => $data['idno'],
            //"avatar" => $data['avatar'],
            "company" => $data['company'],
            "work_name" => $data['work_name'],
            "position_name" => $data['position_name'],

            "update_at" => time(),
            "create_at" => time(),
        );
    }

}
